package fregat.connector.worker;

import fregat.base.database.DatabaseConnector;
import fregat.base.util.StopWatch;
import fregat.connector.ConnectorService;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

/**
 * Base class for workers that implement the iphost logic.
 *
 * @author Alexander Mann <br><b><i>ECO_L O G</i> &nbsp Logistiksysteme GmbH</b>
 * @version 1.0
 */
public class DatabaseWorker extends DefaultWorker {
  protected DatabaseConnector databaseConnector; // connector to database

  protected String quitKey = "quit"; // key for quit statement
  protected String processKey = "process"; // key for process statement
  protected String insertKey = "insert"; // key for insert statement
  protected String messageKey = "message"; // key for message statement

  protected String processSQL; // SQL string for processing data in database
  protected String quitSQL; // SQL string for creating quit telegram in database
  protected String insertSQL; // SQL statement for inserting the received data into database
  protected String messageSQL; // SQL string for processing data in database in combination with an alert message

  /**
   * Prepares the database connection and the SQL strings for processing data
   * and creating quit telegrams.<br>
   * If no configuration for database connection is found default
   * connection 'sysadm@lms' with password 'sysadm' is used.
   *
   * @param name Configuration name of worker
   */
  public DatabaseWorker(String name) {
    super(name);

    // create connector -->
    String configPath = "worker.config." + getName();
    databaseConnector = configuration.configureDatabase(configPath);
    // <--

    // configure SQL strings used by both workers -->
    configPath += ".db";
    processSQL = configuration.getConfigurationString(configPath + ".process");
    quitSQL = configuration.getConfigurationString(configPath + ".quit");
    insertSQL = configuration.getConfigurationString(configPath + ".insert");
    messageSQL = configuration.getConfigurationString(configPath + ".message");

    if (quitSQL != null) {
      quitKey += " -> " + quitSQL + "(?,?,'?')";
      quitSQL = "begin ? := " + quitSQL + "(?,?,?); end;";
    }

    if (processSQL != null) {
      processKey += " -> " + processSQL + "(?)";
      processSQL = "begin ? := " + processSQL + "(?); end;";
    }

    if (messageSQL != null) {
      messageKey += " -> " + messageSQL + "(?,'?')";
      messageSQL = "begin ? := " + messageSQL + "(?,?); end;";
    }

    if (insertSQL != null) {
      insertKey += " -> " + insertSQL + "('?','?')";
      insertSQL = "begin ? := " + insertSQL + "(?,?); end;";
    }
    // <--
  }

  /**
   * Executes the given procedure statement and logs key and execution time.
   *
   * @param log Logging output
   * @param key Key of statement
   * @throws SQLException
   */
  protected void executeStatement(String log, String key) throws SQLException {
    long executionTime = databaseConnector.executeStatement(key);
    if (StopWatch.isEnabled()) {
      ConnectorService.note(getName() + ", " + log + " " + executionTime + "[ms]");
    }
  }

  /**
   * Eexcutes the given query statement and logs key and execution time.
   *
   * @param log Logging output
   * @param key Key of statement
   * @return The result set of the query (empty enumeration if result is <code>null</code>
   * @throws SQLException
   */
  protected ResultSet executeQuery(String log, String key) throws SQLException {
    StopWatch.resetTime(key);
    ResultSet result = databaseConnector.executeQuery(key);
    if (StopWatch.isEnabled()) {
      ConnectorService.note(getName() + ", '" + log + "' " + StopWatch.takeTime(key) + "[ms]");
    }
    return result;
  }

  /**
   * Inserts statement ID as comment.
   *
   * @param sql SQL Statement (select ...)
   * @param id  Statement ID
   * @return Statement with ID or null if invalid statement
   */
  protected String setSelectID(String sql, String id) {
    String str = null;
    int position = sql.toUpperCase().indexOf("SELECT");
    if (position >= 0) {
      position += 6;
      str = sql.substring(0, position) + " /* " + id + " */" + sql.substring(position, sql.length());
    }
    return str;
  }

  /**
   * Processes data with the given ID in database.
   *
   * @param id ID of data
   * @param message Alert message
   * @return ID of created data
   * @throws SQLException
   */
  protected long processData(long id, String message) throws SQLException {
    if(message != null && messageSQL != null) {
      return processMessage(id,message);
    } else {
      return processData(id);
    }
  }

  /**
   * Processes data with the given ID in database with the process statement
   *
   * @param id ID of data
   * @return ID of created data
   * @throws SQLException
   */
  private long processData(long id) throws SQLException {
     CallableStatement statement = databaseConnector.getStatement(processKey);

    if (statement == null) {
      statement = databaseConnector.prepareStatement(processKey, processSQL);
      statement.registerOutParameter(1, Types.INTEGER);
    }
    statement.setLong(2, id);

    executeStatement(DatabaseConnector.logStatement(processKey, new String[]{Long.toString(id)}),
        processKey);

    return statement.getLong(1);
  }

  /**
   * Processes data with the given ID in database with the message statement
   *
   * @param id ID of data
   * @param message Alert message
   * @return ID of created data
   * @throws SQLException
   */
  private long processMessage(long id, String message) throws SQLException {
    CallableStatement statement = databaseConnector.getStatement(messageKey);

    if (statement == null) {
      statement = databaseConnector.prepareStatement(messageKey, messageSQL);
      statement.registerOutParameter(1, Types.INTEGER);
    }
    statement.setLong(2, id);
    statement.setString(3, message);

    executeStatement(DatabaseConnector.logStatement(messageKey, new String[]{Long.toString(id),message}),
        messageKey);

    return statement.getLong(1);
  }

  /**
   * Transfers the given data to database using the given statement.
   *
   * @param channelName name of channel where data was received
   * @param data        data to insert
   * @return ID of inserted data
   * @throws SQLException
   */
  protected long insertData(String channelName, String data) throws SQLException {
    CallableStatement statement = databaseConnector.getStatement(insertKey);

    if (statement == null) {
      statement = databaseConnector.prepareStatement(insertKey, insertSQL);
      statement.registerOutParameter(1, Types.INTEGER);
    }

    statement.setString(2, channelName);
    statement.setString(3, data);

    executeStatement(DatabaseConnector.logStatement(insertKey, new String[]{channelName, data}),
        insertKey);

    return statement.getLong(1);
  }

  /**
   * Fetch a result set from database using the given statement.
   *
   * @param key Key of statement
   * @param sql SQL of statement
   * @return Fetched result set
   * @throws SQLException
   */
  protected ResultSet fetchResult(String key, String sql) throws SQLException {

    CallableStatement statement = databaseConnector.getStatement(key);

    if (statement == null) {
      statement = databaseConnector.prepareStatement(key, sql);
    }

    ResultSet result = executeQuery(key, key);

    databaseConnector.commit();
    return result;
  }
}